﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Configuration;
using Dapper;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class RoleInfoDAL
    {
        public static readonly string SqlConnString = ConfigurationManager.ConnectionStrings["ELCO_ConnectionString"].ConnectionString;

        /// <summary>
        /// 获取客户名称
        /// </summary>
        /// <returns></returns>
        public static List<CustomerInfo> initCustomerInfo(string CustomerName)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                if(CustomerName=="宏大"){
                    string str = "select * from CustomInfo";
                    return conn.Query<CustomerInfo>(str).ToList();
                }
                else
                {
                    string str = "select * from CustomInfo where CustomName='"+CustomerName.ToString().Trim()+"'";
                    return conn.Query<CustomerInfo>(str).ToList();

                }
            }

        }

        /// <summary>
        /// 保存角色信息
        /// </summary>
        /// <returns></returns>
        public static string addRoleInfo(string RoleName, string CustomName, string Remark, List<string> quanxian)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlTransaction transaction = null;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter[] sqlPara = new SqlParameter[5];
                    string limitno = string.Join(",", quanxian.ToArray());

                    sqlPara[0] = new SqlParameter("@RoleName", RoleName);
                    sqlPara[0].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[1] = new SqlParameter("@CustomName", CustomName);
                    sqlPara[1].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[2] = new SqlParameter("@Remark", Remark);
                    sqlPara[2].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[3] = new SqlParameter("@Limitno ", limitno);
                    sqlPara[3].Size = -1;
                    sqlPara[3].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[4] = new SqlParameter("@CatchFlag", 0);
                    sqlPara[4].Size = 10;
                    sqlPara[4].Direction = System.Data.ParameterDirection.Output;

                    foreach (SqlParameter para in sqlPara)
                    {
                        cmd.Parameters.Add(para);
                    }
                    cmd.CommandText = "[usp_UserM_AddRoleInfo]";
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                    if (int.Parse(sqlPara[4].Value.ToString()) == 1)
                    {
                        return "hasexit";
                    }
                    return "success";
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return "falut";
                }
            }
            
        }

        /// <summary>
        /// 保存编辑角色信息
        /// </summary>
        /// <param name="RoleID"></param>
        /// <param name="RoleName"></param>
        /// <param name="Remark"></param>
        /// <param name="quanxian"></param>
        /// <returns></returns>
        public static string editRoleInfo(string RoleID,string RoleName,  string Remark, List<string> quanxian)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlTransaction transaction = null;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter[] sqlPara = new SqlParameter[5];
                    string limitno = string.Join(",", quanxian.ToArray());

                    sqlPara[0] = new SqlParameter("@RoleID", RoleID);
                    sqlPara[0].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[1] = new SqlParameter("@RoleName", RoleName);
                    sqlPara[1].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[2] = new SqlParameter("@Remark", Remark);
                    sqlPara[2].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[3] = new SqlParameter("@Limitno ", limitno);
                    sqlPara[3].Size = -1;
                    sqlPara[3].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[4] = new SqlParameter("@CatchFlag", 0);
                    sqlPara[4].Size = 10;
                    sqlPara[4].Direction = System.Data.ParameterDirection.Output;

                    foreach (SqlParameter para in sqlPara)
                    {
                        cmd.Parameters.Add(para);
                    }
                    cmd.CommandText = "[usp_UserM_EditRoleInfo]";
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                    if (int.Parse(sqlPara[4].Value.ToString()) == 1)
                    {
                        return "hasexit";
                    }
                    return "success";
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return "falut";
                }
            }

        }

        /// <summary>
        /// 获取角色列表
        /// </summary>
        /// <param name="sidx"></param>
        /// <param name="sord"></param>
        /// <param name="keyvalue"></param>
        /// <returns></returns>
        public static DataTable getRoleInfo(string sortname, string sortorder, string keyvalue, string CustomerName)
        {
            string str = "";
            DataTable dt = new DataTable();
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;
                if(CustomerName=="宏大")
                {
                    str = "select * from UserM_RoleInfo where 1=1 ";
                    if (keyvalue != "")
                    {
                        str += " and RoleName like '%" + keyvalue + "%'";
                    }
                    str += "order by " + sortname + " " + sortorder + "";
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = str;
                    SqlDataAdapter Datapter = new SqlDataAdapter(cmd);
                    Datapter.Fill(dt);
                    return dt;
                }
                else
                {
                    str = "select * from UserM_RoleInfo where CustomName='"+CustomerName+"'";
                    if (keyvalue != "")
                    {
                        str += " and RoleName like '%" + keyvalue + "%'";
                    }
                    str += "order by " + sortname + " " + sortorder + "";
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = str;
                    SqlDataAdapter Datapter = new SqlDataAdapter(cmd);
                    Datapter.Fill(dt);
                    return dt;

                }
                
            }

        }

        /// <summary>
        /// 根据ID获取角色信息   编辑角色时候用
        /// </summary>
        /// <param name="RoleID"></param>
        /// <returns></returns>
        public static List<RoleInfoWithLimit> getRoleInfoByID(string RoleID)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                string str = "select role.ID,role.RoleName,role.CustomName,role.Remark,limit.MenuNo from UserM_RoleInfo role left join UserM_RoleLimit limit on role.ID=limit.RoleID where role.ID='" + RoleID + "'";
               return conn.Query<RoleInfoWithLimit>(str).ToList();         
            }

        }

        /// <summary>
        /// 删除角色信息
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static int deleteRoleInfo(string RoleID)
        {
            int affecgtRow = 0;

            using (var conn = new SqlConnection(SqlConnString))
            {
                string sql = "DELETE FROM  UserM_RoleInfo WHERE ID = @RoleID;";
                affecgtRow = conn.Execute(sql, new { RoleID = RoleID });
            }
            return affecgtRow;
        }
    }
}
